<?php         
class Menu_item_model extends MY_Model {
    var $table_name = 'menu_item';
    var $primary_key = 'id';

    function getSelectStatment($data){
        $sqlSelect = "";
        if (isset($data['position_item'])){
            if ($data['position'] == 'after'){
                $sqlSelect = "SELECT rgt as pNo FROM {$this->table_name} WHERE id = '{$data['position_item']}';";
            }else{
                $sqlSelect = "SELECT lft-1 as pNo FROM {$this->table_name} WHERE id = '{$data['position_item']}';";
            }
        }else{
            $sqlSelect = "SELECT lft as pNo FROM {$this->table_name} WHERE id = '{$data['parent_id']}';";
        }
        $data = $this->db->query($sqlSelect)->result_array();
        return $data[0]['pNo'];
    }

    function insert($data,$id=0)
    {
        $this->db->trans_begin();
            $pNo = $this->getSelectStatment($data);
            $sqlUpdate = "UPDATE {$this->table_name} SET rgt = rgt + 2 WHERE rgt > {$pNo} and menu_id = {$data['menu_id']}";
            $this->db->query($sqlUpdate);
            $sqlUpdate = "UPDATE {$this->table_name} SET lft = lft + 2 WHERE lft > {$pNo} and menu_id = {$data['menu_id']}";
            $this->db->query($sqlUpdate);
            if (empty($id)){
                $sql = "INSERT INTO {$this->table_name}(title, link, menu_id,lft,rgt) VALUES('{$data['title']}','{$data['link']}','{$data['menu_id']}', ".($pNo + 1).", ".($pNo + 2).")";
            	$this->db->query($sql);
            	$query = $this->db->query('SELECT LAST_INSERT_ID() AS last_id');
            	$result = $query->row_array();  
				$id = $result['last_id'];
    		}else {
                $sql = "INSERT INTO {$this->table_name}(id,title, link, menu_id,lft,rgt) VALUES('{$id}','{$data['title']}','{$data['link']}','{$data['menu_id']}', ".($pNo + 1).", ".($pNo + 2).")";
            	$this->db->query($sql);
                
            }
            
    		
        if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
            return false;            
        }
        else
        {
            $this->db->trans_commit();
            return $id;
        }
    }
    function  delete($id) {
        $this->db->trans_begin();
            $sql = "SELECT lft, rgt, (rgt - lft + 1) as width,menu_id
                    FROM {$this->table_name}
                    WHERE id = '{$id}'";         
            $node = $this->db->query($sql)->result_array();
            $node = $node[0];
            $sql = "DELETE FROM {$this->table_name} WHERE id = {$id}";
            $this->db->query($sql);

            $sql = "UPDATE {$this->table_name} SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN {$node['lft']} AND {$node['rgt']} AND menu_id = {$node['menu_id']}";
            $this->db->query($sql);

            $sql = "UPDATE {$this->table_name} SET rgt = rgt - 2 WHERE rgt > {$node['rgt']} AND menu_id = {$node['menu_id']}";
            $this->db->query($sql);

            $sql = "UPDATE {$this->table_name} SET lft = lft - 2 WHERE lft > {$node['rgt']} AND menu_id = {$node['menu_id']}";
            $this->db->query($sql);
          
        if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
            return false;
        }
        else
        {
            $this->db->trans_commit();  
            return true;          
        }
    }
	function  delete_all_child($id) {
        $this->db->trans_begin();
            $sql = "SELECT lft, rgt, (rgt - lft + 1) as width,menu_id
                    FROM {$this->table_name}
                    WHERE id = '{$id}'";         
            $node = $this->db->query($sql)->result_array();
            $node = $node[0];
            $sql = "DELETE FROM {$this->table_name} WHERE lft BETWEEN {$node['lft']} AND {$node['rgt']} AND menu_id = {$node['menu_id']}";
            $this->db->query($sql);

            $sql = "UPDATE {$this->table_name} SET rgt = rgt - {$node['width']} WHERE rgt > {$node['lft']} AND menu_id = {$node['menu_id']}";
            $this->db->query($sql);

            $sql = "UPDATE {$this->table_name} SET lft = lft - {$node['width']} WHERE lft > {$node['rgt']} AND menu_id = {$node['menu_id']}";
            $this->db->query($sql);          
          
        if ($this->db->trans_status() === FALSE)
        {
            $this->db->trans_rollback();
        }
        else
        {
            $this->db->trans_commit();            
        }
    }
    function getchild($id,$menu_id,$current_edit_node = null){
        if ($current_edit_node != null) $current_edit_node = "AND node.id != {$current_edit_node}";
        $sql = "SELECT node.title,node.id,node.lft,node.rgt
                FROM {$this->table_name} AS node, {$this->table_name} AS parent, {$this->table_name} AS tmp
                WHERE node.lft BETWEEN tmp.lft AND tmp.rgt
                AND parent.lft BETWEEN tmp.lft AND tmp.rgt
                AND node.lft BETWEEN parent.lft AND parent.rgt
                AND tmp.id = {$id}
                {$current_edit_node}
                AND node.menu_id = {$menu_id}
                AND parent.menu_id = {$menu_id}
                AND tmp.menu_id = {$menu_id}
                GROUP BY node.id
                HAVING count(parent.id)-1 = 1
                ORDER by node.lft";
        return $this->db->query($sql)->result_array();
    }
    function getallchild($id,$menu_id){
        $sql = "SELECT node.title,node.id
                FROM {$this->table_name} AS node, {$this->table_name} AS parent, {$this->table_name} AS tmp
                WHERE node.lft BETWEEN tmp.lft AND tmp.rgt
                AND parent.lft BETWEEN tmp.lft AND tmp.rgt
                AND node.lft BETWEEN parent.lft AND parent.rgt
                AND tmp.id = {$id}
                AND node.menu_id = {$menu_id}
                AND parent.menu_id = {$menu_id}
                AND tmp.menu_id = {$menu_id}
                GROUP BY node.id
                ORDER by node.lft";
        return $this->db->query($sql)->result_array();
    }
    function getAllTree($menu_id,$sysbol = '-- ',$option = false,$number = 1){
        $where = $option ? "AND node.lft !=1 ": "";
        $sql = "SELECT CONCAT(REPEAT('{$sysbol}', COUNT(parent.id) - {$number}), node.title) AS title,node.id,node.link
                FROM {$this->table_name} AS node,
                    {$this->table_name} AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND parent.menu_id = {$menu_id}
                AND node.menu_id = {$menu_id}
                {$where}
                GROUP BY node.title
                ORDER BY node.lft";
        return $this->db->query($sql)->result_array();
    }

    function getNodebyRight($right,$menu_id){
        $this->db->where('rgt',$right);
        $this->db->where('menu_id',$menu_id);
        $query = $this->db->get($this->table_name);
        return $query->row_array();
    }

    function getNodebyLeft($left,$menu_id){
        $this->db->where('lft',$left);
        $this->db->where('menu_id',$menu_id);
        $query = $this->db->get($this->table_name);
        return $query->row_array();
    }

    function getParent($id){
        $this->db->where('id',$id);
        $query = $this->db->get($this->table_name);
        $c_node = $query->row_array();
        $sql = "
            SELECT parent.*
            FROM {$this->table_name} AS node,
            {$this->table_name} AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            AND node.id = {$id}
            AND node.menu_id  = {$c_node['menu_id']}
            AND parent.menu_id  = {$c_node['menu_id']}
            AND parent.id  != {$id}
            ORDER BY parent.lft desc
            limit 1
        ";
        return $this->db->query($sql)->row_array();
    }
    function  updatemenu($data, $id) {
        if($this->delete($id)){
        	return $this->insert($data, $id);
        }
    }

    function getMenuTree($menu_id){
        $sql = "SELECT COUNT(parent.id)-1 as depth,node.*
                FROM {$this->table_name} AS node,
                    {$this->table_name} AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND parent.menu_id = {$menu_id}
                AND node.menu_id = {$menu_id}
                AND node.lft != 1
                GROUP BY node.title
                ORDER BY node.lft";
        return $this->db->query($sql)->result_array();
    }
	function getMenuTreeByLanguage($title,$language){
        $sql = "SELECT COUNT(parent.id)-1 as depth,node.*
                FROM {$this->table_name} AS node,
                    {$this->table_name} AS parent, menu
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND menu.title = '{$title}' AND menu.language = '{$language}' 
                AND parent.menu_id = menu.id
                AND node.menu_id = menu.id 
                AND node.lft != 1
                GROUP BY node.title
                ORDER BY node.lft";
        return $this->db->query($sql)->result_array();
    }
	public function move($current_node_id, $new_parent_id, $menu_id) {

        // this node has not changed parent
        if ($current_node_id == $new_parent_id)
            return true;

        $table_name = $this->table_name;
        $primary_key = $this->primary_key;

        // get parent node and current node
        $this->db->select("node.lft,node.rgt,node.$primary_key,COUNT(parent.$primary_key) -1 AS depth");
        $this->db->from("{$this->table_name} AS node");
        $this->db->from("{$this->table_name} AS parent");
        $this->db->where("node.`$primary_key` in ($current_node_id,$new_parent_id)");
        $this->db->where("node.lft BETWEEN parent.lft AND parent.rgt");
        $this->db->where("node.menu_id = $menu_id AND parent.menu_id = $menu_id");
        $this->db->group_by("node.$primary_key");
        $this->db->order_by("node.$primary_key ASC");

		
		$nodes =  $this->db->get()->result_array();		       

        // check 2 nodes in tree
        if (count($nodes) != 2)
            return false;

        if ($nodes[0][$primary_key] == $current_node_id) {
            $current_node = $nodes[0];
            $parent_node = $nodes[1];
        } else {
            $current_node = $nodes[1];
            $parent_node = $nodes[0];
        }

//        echo "<br>LC: " .
        $left_current = $current_node['lft'];
//        echo "<br>RC: " .
        $right_current = $current_node['rgt'];
//        echo "<br>LP: " .
        $left_parent = $parent_node['lft'];
//        echo "<br>RP: " .
        $right_parent = $parent_node['rgt'];

        // End get parent node and current node

        // check move root
        if ($left_current == 1)
            return false;

        // check do not need to move:
        if (($current_node['depth'] == $parent_node['depth'] + 1) && ($left_current > $left_parent && $left_current < $right_parent))
            return true;




        // get all children
        $select = $this->db->select($primary_key);
            $this->db->from($table_name);
            $this->db->where("`lft` between $left_current AND $right_current");
        	$this->db->where('menu_id',$menu_id);
        $delete_tree = $this->db->get()->result_array();
        /*echo '<pre>';
        print_r($delete_tree);
		echo '</pre>';die;*/
        $id_move = array();

        foreach ($delete_tree as $value) {
        	$id_move[] = $value[$primary_key];
        }

        // check move node to child node
        if (in_array($new_parent_id, $id_move))
            return false;

        $str_id_delete = implode(",", $id_move);

        $num_nodes = count($delete_tree);

        // update left va right.
        if ($left_current < $left_parent) {
            // move from left to right
            $sql = "UPDATE $table_name SET `lft` = `lft` - " . (2 * $num_nodes) . " WHERE `lft` > $left_current AND `lft` <= $right_parent AND $primary_key not in ($str_id_delete) AND `menu_id`=$menu_id";
//            $sql = "UPDATE $table_name SET `left` = `left` - " . (2 * $num_nodes) . " WHERE `left` > $left_current AND `left` <= $left_parent AND $primary_key not in ($str_id_delete)";
            $this->db->query($sql);

            $sql = "UPDATE $table_name SET `rgt` = `rgt` - " . (2 * $num_nodes) . " WHERE `rgt` > $right_current AND `rgt` < $right_parent AND $primary_key not in ($str_id_delete) AND `menu_id`=$menu_id";
            $this->db->query($sql);

            // update nodes has removed
            $delta = $left_current - $right_parent;

            $sql = "UPDATE $table_name SET `lft` = `lft` - $delta - " . (2*$num_nodes) . ", `rgt` = `rgt` - $delta - " . (2*$num_nodes) . " WHERE $primary_key in ($str_id_delete) AND `menu_id`=$menu_id";
            $this->db->query($sql);
        } else {
            if($left_current > $right_parent) {
                // move from right to left
                $sql = "UPDATE $table_name SET `lft` = `lft` + " . (2 * $num_nodes) . " WHERE `lft` > $right_parent AND `lft` < $left_current AND $primary_key not in ($str_id_delete) AND `menu_id`=$menu_id";
                $this->db->query($sql);

                $sql = "UPDATE $table_name SET `rgt` = `rgt` + " . (2 * $num_nodes) . " WHERE `rgt` >= $right_parent AND `rgt` < $right_current AND $primary_key not in ($str_id_delete) AND `menu_id`=$menu_id";
                $this->db->query($sql);

                // update nodes has removed
                $delta = $left_current - $right_parent;

                $sql = "UPDATE $table_name SET `lft` = `lft` - $delta , `rgt` = `rgt` - $delta WHERE $primary_key in ($str_id_delete) AND `menu_id`=$menu_id";
                $this->db->query($sql);
            } else {
//                echo "Move inside";
                // move from child to super parent

                /**
                 * UPDATE VALUE
                 *
                 * Tree move: L,R = L,R - (delta - 1)
                 *
                 * All left node of tree move
                 *      L = L + delta  (all left node)
                 *      R = R + delta ( node has condition Lp < R < Lc)
                 *
                 */
                $delta = $left_current - $left_parent;

//                die;
                //update tree want to move
                $sql = "UPDATE $table_name SET `lft` = `lft` - " . ($delta - 1) . ", `rgt` = `rgt` - " . ($delta - 1) . "  WHERE $primary_key in ($str_id_delete) AND `menu_id`=$menu_id";
                $this->db->query($sql);

                //update left node of current

                //update left value
                $sql = "UPDATE $table_name SET `lft` = `lft` + " . (2 * $num_nodes) . " WHERE `lft` > $left_parent AND `lft` < $left_current AND $primary_key not in ($str_id_delete) AND `menu_id`=$menu_id";
                $this->db->query($sql);

                //update right value
                $sql = "UPDATE $table_name SET `rgt` = `rgt` + " . (2 * $num_nodes) . " WHERE `rgt` > $left_parent AND `rgt` < $left_current AND $primary_key not in ($str_id_delete) AND `menu_id`=$menu_id";
                $this->db->query($sql);
            }
        }
    }
	public function order($moveNodeID, $refNodeID, $menu_id, $position = 1) {
        // Move Node1 after/before  Node2 (left to right)
        // If move left to right => sign = 1 else sign = -1
        // N: Count total node will reorder (node move and child)
        // Get LeftNode1, RightNode1, RightNode2
        // Get AllID of node between LeftNode1 and RightNode2
        // Update node and child reorder: (WHERE ID in AllID Move)
        //    Delta = RightNode2 - LeftNode1 + 1
        //    Left/Right = Left/Right + sign x Delta
        // Update the other node (WHERE ID in AllID)
        //    Left/Right - sign x 2xN

        if (!is_numeric($moveNodeID) || !is_numeric($refNodeID)) {
            return false;
        }

        $table_name = $this->table_name;
        $primary_key = $this->primary_key;
        

        $this->db->select();
        $this->db->from($table_name);
        $this->db->where("$primary_key IN ($moveNodeID, $refNodeID)");
        $this->db->where('menu_id',$menu_id);
        $this->db->order_by("$primary_key ASC");

        $data = $this->db->get()->result_array();		

        if (count($data) != 2)
            return false; // 1 or 2 node not in tree

        if ($moveNodeID < $refNodeID) {
            $moveNode = $data[0];
            $refNode = $data[1];
        } else {
            $moveNode = $data[1];
            $refNode = $data[0];
        }

        if ($moveNode['lft'] < $refNode['lft']) {
            // Move Right;
            // move left to right
            $sign = 1;

            $minLeft = $moveNode['lft'];
            $maxRight = $refNode['rgt'];
            $delta = $refNode['rgt'] - $moveNode['lft'] + 1;
        } else {
            // Move Left
    		// move right to left
            $sign = -1;
            $minLeft = $refNode['lft'];
            $maxRight = $moveNode['rgt'];
            $delta = $moveNode['rgt'] - $refNode['lft'] + 1;
        }
		
        // get all node will be change left and right
        $where = "`lft` >= $minLeft AND `lft` <= $maxRight"; 
        $this->db->select($primary_key);
		$this->db->from($table_name);        
        $this->db->where("`lft` >= $minLeft AND `lft` <= $maxRight");
        $this->db->where('menu_id',$menu_id);   

        $allNodeRef = $this->db->get()->result_array();
		
        $allNodeID = array();
        foreach($allNodeRef as $node):
            array_push($allNodeID, $node[$primary_key]);
        endforeach;
		/*echo '<pre>';
		print_r($allNodeID);
		echo '</pre>';die;*/
        $strIdRef = implode(',', $allNodeID);

        // get all node will move
        $this->db->select($primary_key);
		$this->db->from($this->table_name);        
        $this->db->where("`lft` >= {$moveNode['lft']} AND `lft` <= {$moveNode['rgt']}");
        $this->db->where('menu_id',$menu_id);    
        $allNodeMove = $this->db->get()->result_array();

        $allNodeMoveID = array();
        foreach($allNodeMove as $node):
            array_push($allNodeMoveID, $node[$primary_key]);
        endforeach;
		
        $strIdMove = implode(',', $allNodeMoveID);

        // Update Left/Right = Left/Right + sign x Delta WHERE IN $allNodeMove
        $sql = "UPDATE $table_name SET `lft` = `lft` + " . ($sign * $delta) . ", `rgt` = `rgt` + " . ($sign * $delta) . "
                 WHERE $primary_key in ($strIdMove) AND `menu_id`=$menu_id";
        $this->db->query($sql);

        // N: Count total node will reorder (node move and child)
        //    Left/Right - sign x 2xN
        $N = count($allNodeMoveID);
        $sql = "UPDATE $table_name SET `lft` = `lft` - " . ($sign * 2 * $N) . ", `rgt` = `rgt` - " . ($sign * 2 * $N) . "
                 WHERE $primary_key in ($strIdRef) AND `menu_id`=$menu_id";
        $this->db->query($sql);

        ;

        // fix bug move from left before right
        if ($moveNode['lft'] < $refNode['lft']) {
            if ($position == 1) { // move before
                return $this->order($moveNodeID, $refNodeID, $menu_id, 0);
            }
        } else { // fix bug move from right after left
            if ($position == 2) { // move to right
                return $this->order($moveNodeID, $refNodeID, $menu_id, 0);
            }
        }

        return true;
    }
}
     ?>